package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import Model.Collect;
import utils.DBHelper;

public class CollectDao {
   Connection conn;
   String sql;
   PreparedStatement ps;
   ResultSet rs;
   public CollectDao() {
	   conn = DBHelper.getConnection();
   }
   public ArrayList<Collect> getAll(){
	   sql = "select t1.collect_id as collect_id,t1.user_id as user_id, "
	   		+ "t2.username as username,t1.shop_id as shop_id,"
	   		+ "t3.shopname as shopname,t1.food_id as food_id,"
	   		+ "t4.foodname as foodname,t1.collect_date as collect_date,t1.flag as flag"
	   		+ " from tb_collect t1,tb_user t2,tb_shop t3,tb_food t4 "
	   		+ "where t1.user_id = t2.user_id and t1.shop_id = t3.shop_id and "
	   		+ "t1.food_id = t4.food_id";
	   ArrayList<Collect> list = new ArrayList<Collect>();
	   	try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int collect_id = rs.getInt("collect_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int shop_id = rs.getInt("shop_id");
				String shopname = rs.getString("shopname");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				Timestamp d = rs.getTimestamp("collect_date");
				Date collect_date = new Date(d.getTime());
				int flag = rs.getInt("flag");
				Collect collect = new Collect(collect_id,user_id,username,shop_id,
						shopname,food_id,foodname,collect_date,flag);
				list.add(collect);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   	return list;
   }
   public boolean insert(Collect c) {
	   sql = "insert into tb_collect(user_id,shop_id,food_id,collect_date,flag) "
	   		+ "values(?,?,?,?,?)";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, c.getUser_id());
		ps.setInt(2, c.getShop_id());
		ps.setInt(3, c.getFood_id());
		ps.setTimestamp(4, new Timestamp(c.getCollect_date().getTime()));
		ps.setInt(5, c.getFlag());
		int k = ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   public boolean delete(int id) {
	   sql = "delete from tb_collect where collect_id = ?";
	   try {
		ps = conn.prepareStatement(sql);
		 ps.setInt(1, id);
		int k =  ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return false;
	   
   }
   public ArrayList<Collect> findShopCollect(int user_id) {
	   sql = "select t1.collect_id as collect_id,t1.user_id as user_id, "
		   		+ "t2.username as username,t1.shop_id as shop_id,"
		   		+ "t3.shopname as shopname,t1.food_id as food_id,"
		   		+ "t4.foodname as foodname,t1.collect_date as collect_date,t1.flag as flag"
		   		+ " from tb_collect t1,tb_user t2,tb_shop t3,tb_food t4 "
		   		+ "where t1.user_id = t2.user_id and t1.shop_id = t3.shop_id and "
		   		+ "t1.food_id = t4.food_id and t1.flag = 0 and t1.user_id=?";
	   System.out.println(sql);
	   ArrayList<Collect> list = new ArrayList<Collect>();
	   	try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user_id);
			rs = ps.executeQuery();
			while(rs.next()) {
				int collect_id = rs.getInt("collect_id");
				//int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int shop_id = rs.getInt("shop_id");
				String shopname = rs.getString("shopname");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				Timestamp d = rs.getTimestamp("collect_date");
				Date collect_date = new Date(d.getTime());
				int flag = rs.getInt("flag");
				Collect collect = new Collect(collect_id,user_id,username,shop_id,
						shopname,food_id,foodname,collect_date,flag);
				list.add(collect);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   	return list;
   }
   public ArrayList<Collect> findFoodCollect(int user_id){
	   sql = "select t1.collect_id as collect_id,t1.user_id as user_id, "
		   		+ "t2.username as username,t1.shop_id as shop_id,"
		   		+ "t3.shopname as shopname,t1.food_id as food_id,"
		   		+ "t4.foodname as foodname,t1.collect_date as collect_date,t1.flag as flag"
		   		+ " from tb_collect t1,tb_user t2,tb_shop t3,tb_food t4 "
		   		+ "where t1.user_id = t2.user_id and t1.shop_id = t3.shop_id and "
		   		+ "t1.food_id = t4.food_id and t1.flag = 1 and t1.user_id=?";
	   ArrayList<Collect> list = new ArrayList<Collect>();
	   	try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user_id);
			rs = ps.executeQuery();
			while(rs.next()) {
				int collect_id = rs.getInt("collect_id");
				//int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int shop_id = rs.getInt("shop_id");
				String shopname = rs.getString("shopname");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				Timestamp d = rs.getTimestamp("collect_date");
				Date collect_date = new Date(d.getTime());
				int flag = rs.getInt("flag");
				Collect collect = new Collect(collect_id,user_id,username,shop_id,
						shopname,food_id,foodname,collect_date,flag);
				list.add(collect);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   	return list;
   }
   public boolean isExists(int user_id,int shop_id,int food_id,int flag) {
	   sql = "select t1.collect_id as collect_id,t1.user_id as user_id, "
		   		+ "t2.username as username,t1.shop_id as shop_id,"
		   		+ "t3.shopname as shopname,t1.food_id as food_id,"
		   		+ "t4.foodname as foodname,t1.collect_date as collect_date,t1.flag as flag"
		   		+ " from tb_collect t1,tb_user t2,tb_shop t3,tb_food t4 "
		   		+ "where t1.user_id = t2.user_id and t1.shop_id = t3.shop_id and "
		   		+ "t1.food_id = t4.food_id and  t1.user_id=? and "
		   		+ "t1.shop_id=? and t1.food_id =? and t1.flag = ?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, user_id);
		ps.setInt(2, shop_id);
		ps.setInt(3, food_id);
		ps.setInt(4, flag);
		rs = ps.executeQuery();
		if(rs.next())return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   public ArrayList<Collect> getCollectByCondition(String condition,String value){
	   sql = "select t1.collect_id as collect_id,t1.user_id as user_id, "
		   		+ "t2.username as username,t1.shop_id as shop_id,"
		   		+ "t3.shopname as shopname,t1.food_id as food_id,"
		   		+ "t4.foodname as foodname,t1.collect_date as collect_date,t1.flag as flag"
		   		+ " from tb_collect t1,tb_user t2,tb_shop t3,tb_food t4 "
		   		+ "where t1.user_id = t2.user_id and t1.shop_id = t3.shop_id and "
		   		+ "t1.food_id = t4.food_id and "+condition+" like "+"'%"+value+"%'";
	   System.out.println(sql);
	   ArrayList<Collect> list = new ArrayList<Collect>();
	   	try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int collect_id = rs.getInt("collect_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int shop_id = rs.getInt("shop_id");
				String shopname = rs.getString("shopname");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				Timestamp d = rs.getTimestamp("collect_date");
				Date collect_date = new Date(d.getTime());
				int flag = rs.getInt("flag");
				Collect collect = new Collect(collect_id,user_id,username,shop_id,
						shopname,food_id,foodname,collect_date,flag);
				list.add(collect);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   	return list;
	   
   }
}
